#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2019
# @Author  : linhu (hlf0312@gmail.com)
# @Link    : TBD
# @Version : $Id$

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
from openpyxl.chart import BarChart, Reference, Series

from JiraIssueCommon import buildJiraIssues

teamsCapacity = {
        '1906': {
             'SW':  {
                     'FTH01': 360,
                     'FTH02': 142,
                     'FTH03': 380,
                     'FTH04': 300,
                     'FTH05': 351,
                     'FTH06': 450,
                     'FTH07': 560,
                     'FTH08': 867,
                     'FTH09': 0,
                     'FTH10': 0,
                     'FTH11': 400,
                     'FTH12': 342,
                     'FTH13': 0,
                     'FTH14': 0
                    },
             'I&V': {
                     'FTH01': 0,
                     'FTH02': 175,
                     'FTH03': 169,
                     'FTH04': 176,
                     'FTH05': 160,
                     'FTH06': 0,
                     'FTH07': 0,
                     'FTH08': 0,
                     'FTH09': 0,
                     'FTH10': 360,
                     'FTH11': 0,
                     'FTH12': 0,
                     'FTH13': 223,
                     'FTH14': 398
                    }
                },
        '1905': {
             'SW':  {
                     'FTH01': 600,
                     'FTH02': 365,
                     'FTH03': 0,
                     'FTH04': 402,
                     'FTH05': 426,
                     'FTH06': 550,
                     'FTH07': 490,
                     'FTH08': 388,
                     'FTH09': 330,
                     'FTH10': 0,
                     'FTH11': 500,
                     'FTH12': 0,
                     'FTH13': 0,
                     'FTH14': 0
                    },
             'I&V': {
                     'FTH01': 0,
                     'FTH02': 202,
                     'FTH03': 96,
                     'FTH04': 174,
                     'FTH05': 170,
                     'FTH06': 0,
                     'FTH07': 0,
                     'FTH08': 0,
                     'FTH09': 0,
                     'FTH10': 342,
                     'FTH11': 0,
                     'FTH12': 0,
                     'FTH13': 308,
                     'FTH14': 318
                    }
                },
        '1904': {
             'SW':  {
                     'FTH01': 600,
                     'FTH02': 442,
                     'FTH03': 446,
                     'FTH04': 336,
                     'FTH05': 420,
                     'FTH06': 550,
                     'FTH07': 600,
                     'FTH08': 475,
                     'FTH09': 300,
                     'FTH10': 0,
                     'FTH11': 0,
                     'FTH12': 0,
                     'FTH13': 0,
                     'FTH14': 0
                    },
             'I&V': {
                     'FTH01': 0,
                     'FTH02': 124,
                     'FTH03': 162,
                     'FTH04': 163,
                     'FTH05': 168,
                     'FTH06': 0,
                     'FTH07': 0,
                     'FTH08': 0,
                     'FTH09': 0,
                     'FTH10': 342,
                     'FTH11': 0,
                     'FTH12': 0,
                     'FTH13': 176,
                     'FTH14': 318
                    }
            },
    '1902': {
             'SW':  {
                     'FTH01': 360,
                     'FTH02': 253,
                     'FTH03': 326,
                     'FTH04': 300,
                     'FTH05': 360,
                     'FTH06': 380,
                     'FTH07': 424,
                     'FTH08': 326,
                     'FTH09': 319,
                     'FTH10': 0,
                     'FTH11': 300,
                     'FTH12': 0,
                     'FTH13': 0,
                     'FTH14': 0
                    },
             'I&V': {
                     'FTH01': 0,
                     'FTH02': 171,
                     'FTH03': 126,
                     'FTH04': 162,
                     'FTH05': 150,
                     'FTH06': 0,
                     'FTH07': 0,
                     'FTH08': 0,
                     'FTH09': 0,
                     'FTH10': 230,
                     'FTH11': 0,
                     'FTH12': 0,
                     'FTH13': 400,
                     'FTH14': 215
                    }
            },
        '1903': {
             'SW':  {
                     'FTH01': 600,
                     'FTH02': 448,
                     'FTH03': 432,
                     'FTH04': 410,
                     'FTH05': 430,
                     'FTH06': 550,
                     'FTH07': 600,
                     'FTH08': 484,
                     'FTH09': 0,
                     'FTH10': 0,
                     'FTH11': 500,
                     'FTH12': 342,
                     'FTH13': 0,
                     'FTH14': 0
                    },
             'I&V': {
                     'FTH01': 0,
                     'FTH02': 101,
                     'FTH03': 170,
                     'FTH04': 208,
                     'FTH05': 162,
                     'FTH06': 0,
                     'FTH07': 0,
                     'FTH08': 0,
                     'FTH09': 0,
                     'FTH10': 360,
                     'FTH11': 0,
                     'FTH12': 0,
                     'FTH13': 184,
                     'FTH14': 362
                    }
            }
}

def getFeatureTeamCommitHours(team, workType, jiraIssues):
	commitHours = 0
	if workType == 'SW':
		workTeam = ['4G C-Plane']
	else:
		workTeam = ['4G FT ET', '4G NeVe']

	for jiraIssue in jiraIssues:
		if jiraIssue.featureTeam == team and jiraIssue.competenceArea in workTeam:
			# print(jiraIssue)
			if(jiraIssue.ΣTimeRemaining == 'Null'):
				continue
			commitHours += jiraIssue.ΣTimeRemaining

	return commitHours

def genTeamCapacityVsCommitment(fb, fbCommitExcel):
	print("Handling file: %s" %fbCommitExcel)
	wb = load_workbook(fbCommitExcel)
	ws = wb.active
	jiraIssues = buildJiraIssues(fbCommitExcel)

	teamsCapacityVsCommitmentWs = 'teamsCapacityVsCommitmentWs'
	if teamsCapacityVsCommitmentWs in wb.get_sheet_names():
		# print(wb.get_sheet_names())
		wb.remove_sheet(wb.get_sheet_by_name(teamsCapacityVsCommitmentWs))

	newWs = wb.create_sheet(teamsCapacityVsCommitmentWs)
	newWs.append(['Team', 'SW Capacity', 'I&V Capacity', 'Commit SW Hours', \
		'Commit I&V Hours', 'SW Offset', 'I&V Offset'])

	currenFB = fb
	teams = ['FTH01', 'FTH02', 'FTH03', 'FTH04', 'FTH05', 'FTH06', 'FTH07', \
	         'FTH08', 'FTH09', 'FTH10', 'FTH11', 'FTH12', 'FTH13', 'FTH14']
	for team in teams:
		teamSwCapacity = teamsCapacity[currenFB]['SW'][team]
		teamIvCapacity = teamsCapacity[currenFB]['I&V'][team]
		teamCommitSwHours = getFeatureTeamCommitHours(team, 'SW', jiraIssues)
		teamCommitIvHours = getFeatureTeamCommitHours(team, 'I&V', jiraIssues)
		newWs.append([team, teamSwCapacity, teamIvCapacity, teamCommitSwHours, teamCommitIvHours, \
			teamSwCapacity - teamCommitSwHours, teamIvCapacity - teamCommitIvHours])

	table = Table(displayName='CapacityVsCommitTable', ref='A1:G15')
	newWs.add_table(table)

	redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
	newWs.conditional_formatting.add('F2:G15', CellIsRule(operator='greaterThan', formula=[0], stopIfTrue=True, fill=redFill))

	# values = Reference(newWs, min_row=1, min_col=1, max_row=15, max_col=7)
	# chart = BarChart()
	# chart.add_data(values)
	# newWs.add_chart(chart, "H8")

	wb.save(fbCommitExcel)
	print('"Team capacity vs commit result" in sheet %s of %s' %(teamsCapacityVsCommitmentWs, fbCommitExcel))

if __name__ == '__main__':
	currenFB = '1906'
	fbCommitExcel = '1906_20190530174046.xlsx'
	genTeamCapacityVsCommitment(currenFB, fbCommitExcel)